﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Reflection;
using MySql.Data.MySqlClient;
using Common.SQL;
using System.Data;

namespace MRS.DBL.DAL
{
    public partial class RoleDAL
    {
        static log4net.ILog logger = log4net.LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);

        /// <summary>
        /// 判断是否存在
        /// </summary>
        public bool Exists(string roleName)
        {
            StringBuilder strSQL = new StringBuilder();
            strSQL.Append("select count(1) from role");
            strSQL.Append(" where ");
            strSQL.Append(" roleName = @roleName  ");
            MySqlParameter[] parameters = {
					new MySqlParameter("@roleName", MySqlDbType.Int32)
			};
            parameters[0].Value = roleName;


            int count = 0;
            IDbConnection conn = DBPool.Instance().GetConnection();
            try
            {
                count = Convert.ToInt32(SQLHelper.ExecuteScalar(conn, CommandType.Text, strSQL.ToString(), parameters));
            }
            catch (Exception err)
            {
                logger.Error("role.Exists: " + err);
            }
            finally
            {
                DBPool.Instance().FreeConnection(conn);
            }

            return count > 0;
        }

        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int Add( MRS.DBL.Model.RoleModel model)
        {
            IDbConnection conn = null;
            int effectedrows = 0;

            StringBuilder strSQL = new StringBuilder();
            strSQL.Append("insert into role(");
            strSQL.Append("Role,remark,Status");
            strSQL.Append(") values (");
            strSQL.Append("@Role,@remark,@Status");
            strSQL.Append(") ");
            strSQL.Append(";select @@IDENTITY");
            MySqlParameter[] parameters = {
			            new MySqlParameter("@Role", MySqlDbType.VarChar,20) ,            
                        new MySqlParameter("@remark", MySqlDbType.VarChar,200) ,            
                        new MySqlParameter("@Status", MySqlDbType.VarChar,1)             
              
            };

            parameters[0].Value = model.Role;
            parameters[1].Value = model.Remark;
            parameters[2].Value = model.Status;
            conn = DBPool.Instance().GetConnection();

            try
            {
                effectedrows = SQLHelper.Execute(conn, CommandType.Text, strSQL.ToString(), parameters);
            }
            catch (Exception ex)
            {
                logger.Error("role.Add: " + ex);
            }
            finally
            {
                DBPool.Instance().FreeConnection(conn);
            }

            return effectedrows;
        }

        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update( MRS.DBL.Model.RoleModel model)
        {
            StringBuilder strSQL = new StringBuilder();
            strSQL.Append("update role set ");

            strSQL.Append(" Role = @Role , ");
            strSQL.Append(" remark = @remark , ");
            strSQL.Append(" Status = @Status  ");
            strSQL.Append(" where Id=@Id ");

            MySqlParameter[] parameters = {
			            new MySqlParameter("@Id", MySqlDbType.Int32,4) ,            
                        new MySqlParameter("@Role", MySqlDbType.VarChar,20) ,            
                        new MySqlParameter("@remark", MySqlDbType.VarChar,200) ,            
                        new MySqlParameter("@Status", MySqlDbType.VarChar,1)             
              
            };

            parameters[0].Value = model.Id;
            parameters[1].Value = model.Role;
            parameters[2].Value = model.Remark;
            parameters[3].Value = model.Status;

            IDbConnection conn = DBPool.Instance().GetConnection();
            int effectedrows = 0;
            try
            {
                effectedrows = SQLHelper.Execute(conn, CommandType.Text, strSQL.ToString(), parameters);
            }
            catch (Exception ex)
            {
                logger.Error("role.Update: " + ex);
            }
            finally
            {
                DBPool.Instance().FreeConnection(conn);
            }

            return effectedrows > 0;
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(int Id)
        {
            StringBuilder strSQL = new StringBuilder();
            strSQL.Append("delete from role ");
            strSQL.Append(" where Id=@Id");
            MySqlParameter[] parameters = {
					new MySqlParameter("@Id", MySqlDbType.Int32)
			};
            parameters[0].Value = Id;


            IDbConnection conn = DBPool.Instance().GetConnection();
            int effectedrows = 0;
            try
            {
                effectedrows = SQLHelper.Execute(conn, CommandType.Text, strSQL.ToString(), parameters);
            }
            catch (Exception ex)
            {
                logger.Error("role.Delete: " + ex);
            }
            finally
            {
                DBPool.Instance().FreeConnection(conn);
            }

            return effectedrows > 0;
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public  MRS.DBL.Model.RoleModel GetModel(int Id)
        {
            StringBuilder strSQL = new StringBuilder();
            strSQL.Append("select Id, Role, remark, Status  ");
            strSQL.Append("  from role ");
            strSQL.Append(" where Id=@Id");
            MySqlParameter[] parameters = {
					new MySqlParameter("@Id", MySqlDbType.Int32)
			};
            parameters[0].Value = Id;


             MRS.DBL.Model.RoleModel model = null;

            IDbConnection conn = DBPool.Instance().GetConnection();
            DataSet ds = null;

            try
            {
                ds = SQLHelper.QueryDataSet(conn, CommandType.Text, strSQL.ToString(), parameters);
            }
            catch (Exception ex)
            {
                logger.Error("role.GetModel: " + ex);
            }
            finally
            {
                DBPool.Instance().FreeConnection(conn);
            }

            if (ds.Tables[0].Rows.Count > 0)
            {
                model = new  MRS.DBL.Model.RoleModel();

                if (ds.Tables[0].Rows[0]["Id"] != null && ds.Tables[0].Rows[0]["Id"].ToString() != "")
                {
                    model.Id = int.Parse(ds.Tables[0].Rows[0]["Id"].ToString());
                }
                if (ds.Tables[0].Rows[0]["Role"] != null && ds.Tables[0].Rows[0]["Role"].ToString() != "")
                {
                    model.Role = ds.Tables[0].Rows[0]["Role"].ToString();
                }
                if (ds.Tables[0].Rows[0]["remark"] != null && ds.Tables[0].Rows[0]["remark"].ToString() != "")
                {
                    model.Remark = ds.Tables[0].Rows[0]["remark"].ToString();
                }
                if (ds.Tables[0].Rows[0]["Status"] != null && ds.Tables[0].Rows[0]["Status"].ToString() != "")
                {
                    model.Status = ds.Tables[0].Rows[0]["Status"].ToString();
                }
            }

            return model;
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public  MRS.DBL.Model.RoleModel[] GetList()
        {
            IDbConnection conn = null;
            string strSQL = null;
            DataSet ds = null;
            List< MRS.DBL.Model.RoleModel> lstModel = null;

            strSQL = "select * FROM role";

            conn = DBPool.Instance().GetConnection();
            try
            {
                ds = SQLHelper.QueryDataSet(conn, CommandType.Text, strSQL.ToString(), null);
            }
            catch (Exception e)
            {
                logger.Error("role.GetList:" + e);
            }
            finally
            {
                DBPool.Instance().FreeConnection(conn);
            }

            lstModel = new List< MRS.DBL.Model.RoleModel>();

            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                 MRS.DBL.Model.RoleModel model = new  MRS.DBL.Model.RoleModel();

                if (ds.Tables[0].Rows[i]["Id"] != null && ds.Tables[0].Rows[i]["Id"].ToString() != "")
                {
                    model.Id = int.Parse(ds.Tables[0].Rows[i]["Id"].ToString());
                }
                if (ds.Tables[0].Rows[i]["Role"] != null && ds.Tables[0].Rows[i]["Role"].ToString() != "")
                {
                    model.Role = ds.Tables[0].Rows[i]["Role"].ToString();
                }
                if (ds.Tables[0].Rows[i]["remark"] != null && ds.Tables[0].Rows[i]["remark"].ToString() != "")
                {
                    model.Remark = ds.Tables[0].Rows[i]["remark"].ToString();
                }
                if (ds.Tables[0].Rows[i]["Status"] != null && ds.Tables[0].Rows[i]["Status"].ToString() != "")
                {
                    model.Status = ds.Tables[0].Rows[i]["Status"].ToString();
                }

                lstModel.Add(model);
            }

            return lstModel.ToArray();
        }
    }
}
